Create database RAOVAT
go
Use RAOVAT
go
--1 LOAI_NGUOI_DUNG
Create table LOAI_NGUOI_DUNG
(
	MaLoaiNguoiDung int identity(1,1),
	TenLoaiNguoiDung nvarchar(100),		
	Deleted bit,
	CONSTRAINT PK_LOAINGUOIDUNG PRIMARY KEY (MaLoaiNguoiDung),
)
--2 NGUOI_DUNG
Create table NGUOI_DUNG
(
	MaNguoiDung int identity(1,1),
	MaLoaiNguoiDung int,
	TenNguoiDung nvarchar(200),	
	TenDangNhap nchar(100),
	MatKhau nchar(50),
	Email varchar(200),
	DienThoai char(20),
	SoCMND char(20),
	DiaChi nvarchar(100),
	MaKichHoat int,
	TinhTrangKichHoat int,
	ThoiGianDangKy datetime,
	ThoiGianHetHan datetime,
	ViPham bit,
	DiemVIP float,
	Deleted bit,
	CONSTRAINT PK_NGUOIDUNG PRIMARY KEY (MaNguoiDung),
	CONSTRAINT FK_NGUOIDUNG_LOAINGUOIDUNG FOREIGN KEY (MaLoaiNguoiDung)REFERENCES LOAI_NGUOI_DUNG(MaLoaiNguoiDung),
)
--3 LOAI_QUANG_CAO
Create table LOAI_QUANG_CAO
(
	MaLoaiQuangCao int identity(1,1),
	TenLoaiQuangCao nvarchar(100),
	Deleted bit,		
	CONSTRAINT PK_LOAIQUANGCAO PRIMARY KEY (MaLoaiQuangCao),	
)

--4 QUANG_CAO
Create table QUANG_CAO
(
	MaQuangCao int identity(1,1),
	MaLoaiQuangCao int,
	MaNguoiDung int,
	TenQuangCao nvarchar(200),	
	LienKet char(200),
	ThoiGianDang datetime,
	ThoiGianHetHan datetime,
	NoiDungQuangCao nvarchar(200),
	Deleted bit,
	CONSTRAINT PK_QUANGCAO PRIMARY KEY (MaQuangCao),
	CONSTRAINT FK_QUANGCAO_LOAIQUANGCAO FOREIGN KEY (MaLoaiQuangCao) REFERENCES LOAI_QUANG_CAO(MaLoaiQuangCao),
	CONSTRAINT PK_QUANGCAO_NGUOIDUNG FOREIGN KEY (MaNguoiDung) REFERENCES NGUOI_DUNG(MaNguoiDung)
)

--5 DANH_MUC_CHINH
Create table DANH_MUC_CHINH
(
	MaDanhMucChinh int identity(1,1),
	TenDanhMucChinh nvarchar(100),		
	Deleted bit,	
	CONSTRAINT PK_DANHMUCCHINH PRIMARY KEY (MaDanhMucChinh),	
)

--6 DANH_MUC_CON
Create table DANH_MUC_CON
(
	MaDanhMucCon int identity(1,1),
	MaDanhMucChinh int,	
	TenDanhMucCon nvarchar(100),	
	Deleted bit,	
	CONSTRAINT PK_DANHMUCCON PRIMARY KEY (MaDanhMucCon),
	CONSTRAINT FK_DANHMUCCON_DANHMUCCHINH FOREIGN KEY (MaDanhMucChinh) REFERENCES DANH_MUC_CHINH(MaDanhMucChinh),
)

--7 DIA_DIEM
Create table DIA_DIEM
(
	MaDiaDiem int identity(1,1),
	TenDiaDiem nvarchar(200),		
	Deleted bit,	
	CONSTRAINT PK_DIADIEM PRIMARY KEY (MaDiaDiem),	
)

--8 LOAI_TIN_RAO_VAT
Create table LOAI_TIN_RAO_VAT
(
	MaLoaiTinRaoVat int identity(1,1),
	TenLoaiTinRaoVat nvarchar(100),		
	Deleted bit,
	CONSTRAINT PK_LOAITINRAOVAT PRIMARY KEY (MaLoaiTinRaoVat),
)

--9 TIN_RAO_VAT
Create table TIN_RAO_VAT
(
	MaTinRaoVat int identity(1,1),
	MaLoaiTinRaoVat int,
	MaDanhMucCon int,
	MaNguoiDung int,
	MaDiaDiem int,
	TenTinRaoVat nchar(50),	
	Thumbnail image,
	NoiDungTinRaoVat nvarchar(1000),
	ThoiGianDang datetime,
	SoLanXem int,
	DiemBinhChonTB float,
	DaDuyet bit,
	ViPham bit,
	Deleted bit,
	CONSTRAINT PK_TINRAOVAT PRIMARY KEY (MaTinRaoVat),
	CONSTRAINT FK_TINRAOVAT_DIADIEM FOREIGN KEY (MaDiaDiem) REFERENCES DIA_DIEM(MaDiaDiem),
	CONSTRAINT FK_TINRAOVAT_LOAITINRAOVAT FOREIGN KEY (MaLoaiTinRaoVat) REFERENCES LOAI_TIN_RAO_VAT(MaLoaiTinRaoVat),
	CONSTRAINT FK_TINRAOVAT_NGUOIDUNG FOREIGN KEY (MaNguoiDung) REFERENCES NGUOI_DUNG(MaNguoiDung),
	CONSTRAINT FK_TINRAOVAT_DANHMUCCON FOREIGN KEY (MaDanhMucCon) REFERENCES DANH_MUC_CON(MaDanhMucCon),
)

--10 BAI_TRA_LOI
Create table BAI_TRA_LOI
(
	MaBaiTraLoi int identity(1,1),
	MaNguoiDung int,
	MaTinRaoVat int,	
	NoiDungTraLoi nvarchar(500),
	ThoiGianTraLoi datetime,
	Deleted bit,
	CONSTRAINT PK_BAITRALOI PRIMARY KEY (MaBaiTraLoi),
	CONSTRAINT PK_BAITRALOI_NGUOIDUNG FOREIGN KEY (MaNguoiDung) REFERENCES NGUOI_DUNG(MaNguoiDung),
	CONSTRAINT PK_BAITRALOI_TINRAOVAT FOREIGN KEY (MaTinRaoVat) REFERENCES TIN_RAO_VAT(MaTinRaoVat),
)


--11 BINH_CHON
Create table BINH_CHON
(	
	MaTinRaoVat int,	
	MaNguoiDung int,
	ThoiGianBinhChon datetime,
	DiemBinhChon int,
	Deleted bit,
	CONSTRAINT PK_BINH_CHON PRIMARY KEY (MaTinRaoVat,MaNguoiDung),
	CONSTRAINT FK_BINH_CHON_TINRAOVAT FOREIGN KEY (MaTinRaoVat) REFERENCES TIN_RAO_VAT(MaTinRaoVat),
	CONSTRAINT FK_BINH_CHON_NGUOIDUNG FOREIGN KEY (MaNguoiDung) REFERENCES NGUOI_DUNG(MaNguoiDung),
)


--12 TIN_NHAN
Create table TIN_NHAN
(	
	MaTinNhan int identity(1,1),		
	MaNguoiGoi int,
	MaNguoiNhan int,
	TieuDeTinNhan nvarchar(155),
	NoiDungTinNhan nvarchar(500),	
	ThoiGianNhanTin datetime,
	Readed bit,
	Deleted bit,	
	CONSTRAINT PK_TINNHAN PRIMARY KEY (MaTinNhan),
	CONSTRAINT FK_TINNHAN_NGUOIDUNGGOI FOREIGN KEY (MaNguoiGoi) REFERENCES NGUOI_DUNG(MaNguoiDung),
	CONSTRAINT FK_TINNHAN_NGUOIDUNGNHAN FOREIGN KEY (MaNguoiNhan) REFERENCES NGUOI_DUNG(MaNguoiDung),	
)
--13 DANG_KY_NHAN_TIN_RAO_VAT
Create table DANG_KY_NHAN_TIN_RAO_VAT
(
	MaDangKy int identity(1,1),
	MaNguoiDung int,
	Email varchar(200),
	ThoiGianDangKy datetime, 
	Deleted bit,
	CONSTRAINT PK_DANGKYNHANTINRAOVAT PRIMARY KEY (MaDangKy),
	CONSTRAINT FK_DANGKYNHANTINRAOVAT_NGUOIDUNG FOREIGN KEY (MaNguoiDung) REFERENCES NGUOI_DUNG(MaNguoiDung),
	
)

--14 CHI_TIET_NHAN_TIN_RAO_VAT
Create table CHI_TIET_NHAN_TIN_RAO_VAT
(
	MaChiTietNhanTin int identity(1,1), 
	MaDangKy int,
	MaTinRaoVat int,
	ThoiGianGoiTin datetime,
	Deleted bit,
	CONSTRAINT PK_CHITIETNHANTINRAOVAT PRIMARY KEY (MaChiTietNhanTin),
	CONSTRAINT PK_CHITIETNHANTINRAOVAT_DANGKYNHANTINRAOVAT FOREIGN KEY (MaDangKy) REFERENCES DANG_KY_NHAN_TIN_RAO_VAT(MaDangKy),
	CONSTRAINT PK_CHITIETNHANTINRAOVAT_TINRAOVAT FOREIGN KEY (MaTinRaoVat) REFERENCES TIN_RAO_VAT(MaTinRaoVat),
)

--15 BAO_CAO_NGUOI_DUNG_VI_PHAM
Create table BAO_CAO_NGUOI_DUNG_VI_PHAM
(
	MaViPham int identity(1,1),
	MaNguoiViPham int,
	MaNguoiBaoCao int,
	ThoiGianBaoCao datetime,
	ThoiGianCanhCao datetime,
	LyDo nvarchar(100),
	Deleted bit,
	CONSTRAINT PK_NGUOI_DUNG_VI_PHAMM PRIMARY KEY(MaViPham),
	CONSTRAINT FK_NGUOI_DUNG_VI_PHAM_BAOCAONGUOIDUNGVIPHAM FOREIGN KEY (MaNguoiViPham) REFERENCES NGUOI_DUNG(MaNguoiDung),
	CONSTRAINT FK_NGUOI_DUNG_VI_PHAM_BAOCAONGUOIDUNGBAOCAO FOREIGN KEY (MaNguoiBaoCao) REFERENCES NGUOI_DUNG(MaNguoiDung),
)

--16 TIN_RAO_VAT_VI_PHAM
Create table BAO_CAO_TIN_RAO_VAT_VI_PHAM
(
	MaViPham int identity(1,1),
	MaTinViPham int,
	MaNguoiBaoCao int,
	ThoiGianBaoCao datetime,
	Deleted bit,
	CONSTRAINT PY_BAO_CAO_TIN_RAO_VAT_VI_PHAM PRIMARY KEY(MaViPham),
	CONSTRAINT FK_BAO_CAO_TIN_RAO_VAT_VI_PHAM_NGUOIDUNG FOREIGN KEY (MaNguoiBaoCao) REFERENCES NGUOI_DUNG(MaNguoiDung),
	CONSTRAINT FK_BAO_CAO_TIN_RAO_VAT_VI_PHAM_TINRAOVAT FOREIGN KEY (MaTinViPham) REFERENCES TIN_RAO_VAT(MaTinRaoVat),
)

--17 TRUY_CAP
Create table TRUY_CAP
(
	MaTruyCap int identity(1,1),
	MaNguoiDung int,
	ThoiGianDangNhap datetime,		
	ThoiGianDangXuat datetime,
	Deleted bit,	
	CONSTRAINT PK_TRUYCAP PRIMARY KEY (MaTruyCap),
	CONSTRAINT FK_TRUYCAP_NGUOIDUNG FOREIGN KEY (MaNguoiDung) REFERENCES NGUOI_DUNG(MaNguoiDung),
	
)
--18 CHI_TIET_TRUY_CAP
Create table CHI_TIET_TRUY_CAP
(
	MaChiTietTruyCap int identity(1,1),
	MaTruyCap int,
	ThoiGianBatDau datetime,		
	ThoiGianketThuc datetime,
	Deleted bit,	
	CONSTRAINT PK_CHITIETTRUYCAP PRIMARY KEY (MaChiTietTruyCap),
	CONSTRAINT FK_CHITIETTRUYCAP_TRUYCAP FOREIGN KEY (MaTruyCap) REFERENCES TRUY_CAP(MaTruyCap),
	
)

--19 GIAO_DIEN

Create table GIAO_DIEN
(
	MaGiaoDien int identity(1,1),
	MaNguoiDung int,
	TenGiaoDien varchar (100),		
	LinkMasterPage varchar (200),
	Deleted bit,	
	CONSTRAINT PK_GIAODIEN PRIMARY KEY (MaGiaoDien),
	CONSTRAINT FK_GIAODIEN_NGUOIDUNG FOREIGN KEY (MaNguoiDung) REFERENCES NGUOI_DUNG(MaNguoiDung),
	
)


